using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using Microsoft.Data.SqlClient; // 确保已安装 NuGet 包

namespace Ocean.Transfer.Tools.Data
{
    public class SqlServerHelper
    {
        private readonly string _connectionString;

        private readonly SqlConnection _conn; //使用已初始化的 _conn
 
        public SqlServerHelper()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["SQLServerConnection"].ConnectionString;
            _conn = new SqlConnection(_connectionString); 
        }

        public SqlServerHelper(string connectionString)
        {
            _connectionString = connectionString;
            _conn = new SqlConnection(_connectionString);
        }

        public DataTable ExecuteQuery(string sql, params SqlParameter[] parameters)
        {
            using (var cmd = new SqlCommand(sql, _conn)) // 使用已初始化的 _conn
            {
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);

                var dt = new DataTable();
                _conn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
                _conn.Close(); // 确保连接关闭
                return dt;
            }
        }

        public int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            using (var cmd = new SqlCommand(sql, _conn)) // 使用已初始化的 _conn
            {
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);

                _conn.Open();
                var result = cmd.ExecuteNonQuery();
                _conn.Close(); // 确保连接关闭
                return result;
            }
        }

        public object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            using (var cmd = new SqlCommand(sql, _conn)) // 使用已初始化的 _conn
            {
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);

                _conn.Open();
                var result = cmd.ExecuteScalar();
                _conn.Close(); // 确保连接关闭
                return result;
            }
        }

        public void TestConnection()
        {
            _conn.Open();
            _conn.Close();
        }
    }
}